At 15:58 +0300 on 25/04/1999, Jon Barnett wrote:
> It seems that the standard is quite ambiguous as to what it expects and
>doesn't
> address the limit case (what happens for an aggregate function result when a
> table is empty). My SQL Programmer's Reference (ISBN 1-56604-760-9) has
> nothing to say on this matter and I haven't successfully found a copy of the
> SQL92 specifications (seems harder than tracking down a printed copy of
> "Historia Regum Britanniae"). I suspect that the SQL specifications may
>also
> overlook the issue as well. *sigh*
Ah, well, I have a (late draft) of the SQL92 standard. And here is what it
has to say on the matter (note 2-b-i):
<<< begin quotation from standard >>>
General Rules
1) Case:
a) If COUNT(*) is specified, then the result is the cardinality of T.
b) Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each
rowof T and eliminating null values. If one or more null values are eliminated, then a completion condition is
raised:warning- null value eliminated in set function.
2) If DISTINCT is specified, then let TXA be the result of elimi- nating redundant duplicate values from TX.
Otherwise,let TXA be TX.
Case:
a) If the <general set function> COUNT is specified, then the result is the cardinality of TXA.
b) If AVG, MAX, MIN, or SUM is specified, then
Case:
i) If TXA is empty, then the result is the null value.
ii) If AVG is specified, then the result is the average of the values in TXA.
iii) If MAX or MIN is specified, then the result is respec- tively the maximum or minimum value in TXA. These
results are determined using the comparison rules specified in Subclause 8.2, "<comparison predicate>".
iv) If SUM is specified, then the result is the sum of the values in TXA. If the sum is not within the range
ofthe data type of the result, then an exception condition is raised: data exception-numeric value out of
range.
<<< end quotation from standard >>>
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
